/**
 * 
 */
package com.lanswon.qzsmk.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;

import ch.qos.logback.classic.Logger;

import com.ctc.wstx.util.StringUtil;
import com.lanswon.qzsmk.base.CardContants;
import com.lanswon.qzsmk.base.PathContants;
import com.lanswon.qzsmk.dao.NationMapper;
import com.lanswon.qzsmk.exception.ErrorCode;
import com.lanswon.qzsmk.exception.SystemException;
import com.lanswon.qzsmk.model.CorpTmp;
import com.lanswon.qzsmk.model.PersonalTmp;
import com.lanswon.qzsmk.service.systemManageService.ParamManageService;
import com.lanswon.qzsmk.vo.DownloadBatchSellCardVo;

/**
 * 导出表格(批量申请，异常数据批次)
 * 
 * @author sun
 * @desc 2007 excel String fileName = "file.xlsx"
 */
public class ExportExcel {

	private static Logger logger = (Logger) LoggerFactory.getLogger(ExportExcel.class);

	/**
	 * 设置样式
	 * 
	 * @param workbook
	 * @return
	 */
	private static CellStyle setContentStyle(Workbook workbook, Boolean isBold) {
		Font font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 11);
		font.setColor(IndexedColors.BLACK.index);
		font.setBold(isBold);
		CellStyle cs = workbook.createCellStyle();
		cs.setAlignment(HorizontalAlignment.CENTER);
		DataFormat  format = workbook.createDataFormat();
		cs.setDataFormat(format.getFormat("@"));
		
		cs.setFont(font);
		if (!isBold) {
			cs.setBorderBottom(BorderStyle.THIN);
			cs.setBorderLeft(BorderStyle.THIN);
			cs.setBorderRight(BorderStyle.THIN);
			cs.setBorderTop(BorderStyle.THIN);
		}

		return cs;
	}

	/**
	 * 设置表2标题
	 * 
	 * @param sheet
	 * @param workbook
	 */
	private static void setTitle2(Sheet sheet, Workbook workbook) {
		Row rowheader = sheet.createRow(1);
		Cell cellheader = rowheader.createCell(0);
		cellheader.setCellValue("单位基本信息表");
		cellheader.setCellStyle(setContentStyle(workbook, true));
		// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
	}

	/**
	 * 设置表1标题
	 * 
	 * @param sheet
	 * @param workbook
	 */
	private static void setTitle(Sheet sheet, Workbook workbook) {
		Row rowheader = sheet.createRow(2);
		Cell cellheader = rowheader.createCell(0);
		cellheader.setCellValue("市民卡批量申请");
		cellheader.setCellStyle(setContentStyle(workbook, true));
		// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 13));
	}

	/**
	 * 创建表2
	 * 
	 * @param workbook
	 * @param List
	 */
	private static void creSecondSheet(Workbook workbook, CorpTmp cor) {
		Sheet sheet = workbook.createSheet("单位基本信息表");
		setTitle2(sheet, workbook);
		
		Row row = sheet.createRow(0);
		Cell c = row.createCell(0);
		c.setCellValue("注：带*号为必填项");
		// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
		sheet.addMergedRegion(new CellRangeAddress(0,0, 0, 1));
		
		row = sheet.createRow(2);
		c = row.createCell(0);
		c.setCellValue("*单位名称");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		String v = cor.getCorpName();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(cor.getCorpName());// 单位名称
		}else{
			c.setCellValue("");// 单位名称
		}
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(3);
		c = row.createCell(0);
		c.setCellValue("*单位简称");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		 v = cor.getCorpAbbrName();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 单位简称
		}else{
			c.setCellValue("");//单位简称
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(4);
		c = row.createCell(0);
		c.setCellValue("单位类型");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCorpType();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 单位类型
		}else{
			c.setCellValue("");// 单位类型
		}
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(5);
		c = row.createCell(0);
		c.setCellValue("单位等级");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCorpLevel();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 单位等级
		}else{
			c.setCellValue("");// 单位等级
		}
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(6);
		c = row.createCell(0);
		c.setCellValue("*单位地址");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCorpAddr();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 单位地址
		}else{
			c.setCellValue("");// 单位地址
		}
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(7);
		c = row.createCell(0);
		c.setCellValue("*邮政编码");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCorpPostalcode();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 邮政编码
		}else{
			c.setCellValue("");// 邮政编码
		}
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(8);
		c = row.createCell(0);
		c.setCellValue("*联系人");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLinkmanName();
		v = cor.getCorpPostalcode();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(9);
		c = row.createCell(0);
		c.setCellValue("*联系人电话");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLinkmanPhone();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		
		
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(10);
		c = row.createCell(0);
		c.setCellValue("*手机");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLinkmanMobile();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(11);
		c = row.createCell(0);
		c.setCellValue("传真号码");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLinkmanFax();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(12);
		c = row.createCell(0);
		c.setCellValue("*E-Mail");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getEmail();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(13);
		c = row.createCell(0);
		c.setCellValue("*单位负责人");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLeader();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(14);
		c = row.createCell(0);
		c.setCellValue("*负责人电话");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getLeaderPhone();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(15);
		c = row.createCell(0);
		c.setCellValue("法人姓名");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getJpName();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(16);
		c = row.createCell(0);
		c.setCellValue("法人证件类型");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getJpPaperType();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(17);
		c = row.createCell(0);
		c.setCellValue("法人证件号码");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getJpPaperNo();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(18);
		c = row.createCell(0);
		c.setCellValue("法人联系电话");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getJpPhone();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(19);
		c = row.createCell(0);
		c.setCellValue("组织机构代码");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getOrgCode();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(20);
		c = row.createCell(0);
		c.setCellValue("开户银行");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getAccountBank();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellValue(cor.getAccountBank());// 开户银行
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(21);
		c = row.createCell(0);
		c.setCellValue("基本帐号");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getBasicAccount();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(22);
		c = row.createCell(0);
		c.setCellValue("注册资金");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getRegisterAmt().toString();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(23);
		c = row.createCell(0);
		c.setCellValue("注册日期");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		Date d  = cor.getRegisterDate();
		if(d != null){
			c.setCellValue(DateFormatUtils.format(d,"yyyy/MM/dd"));// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));
		
		c = row.createCell(2);
		c.setCellValue("样例：yyyy/MM/dd");// 注册日期
		c.setCellStyle(setContentStyle(workbook, false));
		

		row = sheet.createRow(24);
		c = row.createCell(0);
		c.setCellValue("纳税登记号");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getTaxRegisterNo();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(25);
		c = row.createCell(0);
		c.setCellValue("企业登记号");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCrNo();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(26);
		c = row.createCell(0);
		c.setCellValue("客户经理");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCustManager();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(27);
		c = row.createCell(0);
		c.setCellValue("行业代码");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getIndustryCode();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(28);
		c = row.createCell(0);
		c.setCellValue("单位状态");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getStatus();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

		row = sheet.createRow(29);
		c = row.createCell(0);
		c.setCellValue("*单位编号");
		c.setCellStyle(setContentStyle(workbook, false));
		c = row.createCell(1);
		v = cor.getCorpNo();
		if(StringUtils.isNotEmpty(v)){
			c.setCellValue(v);// 
		}else{
			c.setCellValue("");// 
		}
		c.setCellStyle(setContentStyle(workbook, false));

//		row = sheet.createRow(31);
//		c = row.createCell(0);
//		c.setCellValue("是否加急");
//		c.setCellStyle(setContentStyle(workbook, false));
//		c = row.createCell(1);
//		c.setCellValue(cor.getIsUrgent());// 是否加急
//		c.setCellStyle(setContentStyle(workbook, false));

		// 2列内容
		for (int i = 0; i < 2; i++) {
			// 调整最适合列宽
			sheet.autoSizeColumn(i);
		}
	}

	/**
	 * 创建表1
	 * 
	 * @param workbook
	 * @param List
	 */
	private static void creFirstSheet(Workbook workbook, List<PersonalTmp> list) {
		Sheet sheet = workbook.createSheet("市民卡批量申请表");

		setTitle(sheet, workbook);
		
		Row row1 = sheet.createRow(0);
		Cell c = row1.createCell(0);
		c.setCellValue("注：带*号为必填项，填写内容时，将样例数据修改为正式数据，并且单元格格式均为文本格式。");
		// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
		sheet.addMergedRegion(new CellRangeAddress(0,0, 0, 13));
		
		
		Row row2 = sheet.createRow(1);
		 c = row2.createCell(0);
		c.setCellValue("样例：序号1数据。");
		// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
		sheet.addMergedRegion(new CellRangeAddress(1,1, 0, 1));
		
		Row row3 = sheet.createRow(3);
		
		String[] tableTitle = { "*序号", "*证件类型", "*证件号码", "*姓名", "*性别", "*民族", "*出生日期", "*工作单位", "*家庭电话", "*单位电话", "*手机", "*通讯地址",
				"*邮政编码", "错误标记信息" };

		for (int i = 0; i < tableTitle.length; i++) {
			Cell cell = row3.createCell(i);
			cell.setCellStyle(setContentStyle(workbook, false));
			cell.setCellValue(tableTitle[i]);
			
		}

		ParamManageService pm = SpringContextHolder.getBean(ParamManageService.class);
		NationMapper nm = SpringContextHolder.getBean(NationMapper.class);

		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + 4);
			PersonalTmp pt = list.get(i);

			 c = row.createCell(0);
			c.setCellValue(i + 1);
			c.setCellType(CellType.NUMERIC);
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(1);
			c.setCellType(CellType.STRING);

			try {
				String paperame = pm.queryPaperByPaperTypeCode(pt.getPaperType()).getCertificatesName();
				System.out.println(paperame);
				c.setCellValue(paperame);// 证件code转换证件name
			} catch (Exception e) {
				e.printStackTrace();
				c.setCellValue("未识别证件");// 证件code转换证件name
			}

			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(2);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getPaperNo());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(3);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getCustomerName());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(4);
			c.setCellType(CellType.STRING);
			c.setCellValue(

			pt.getGender() == CardContants.GENDER_MALE ? "男" : "女"

			);
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(5);
			c.setCellType(CellType.STRING);
			c.setCellValue(nm.queryNationByCode(pt.getNationality()).getNationname());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(6);
			c.setCellType(CellType.NUMERIC);
			c.setCellValue(DateFormatUtils.format(pt.getBirthday(), "yyyy/MM/dd"));
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(7);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getCorpName());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(8);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getHomePhone());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(9);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getOfficePhone());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(10);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getMobile());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(11);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getContact());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(12);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getCurPostalcode());
			System.out.println("ddddddddddddddddddddddd" + pt.getCurPostalcode());
			c.setCellStyle(setContentStyle(workbook, false));
			c = row.createCell(13);
			c.setCellType(CellType.STRING);
			c.setCellValue(pt.getErrorInfo());
			c.setCellStyle(setContentStyle(workbook, false));
		}

		// 13列内容
		for (int i = 0; i < 14; i++) {
			// 调整最适合列宽
			sheet.autoSizeColumn(i);
		}

	}

	/**
	 * 导出
	 * 
	 * @param List
	 * @param filePath
	 * @throws Exception
	 */
	public static void export(List<PersonalTmp> list, String filePath, CorpTmp c) throws Exception {
		OutputStream os = null;
		XSSFWorkbook Workbook = null;
		try {
			os = new FileOutputStream(filePath);

			 Workbook = new XSSFWorkbook();

			creFirstSheet(Workbook, list);
			creSecondSheet(Workbook, c);
			Workbook.write(os);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			String msg1="系统找不到文件异常";
			logger.error(msg1);
			throw new SystemException(SystemException.FileNotFoundException, msg1);

		} catch (IOException e) {
			e.printStackTrace();
			String msg1="IO流异常";
			logger.error(msg1);
			throw new SystemException(SystemException.IOException, msg1);

		} finally {
			try {
			if (os != null){
				os.flush();
				os.close();
			}
			if(Workbook != null ){
					Workbook.close();
			}
			} catch (IOException e) {
				e.printStackTrace();
				String msg1="IO流关闭异常";
				logger.error(msg1);
				throw new SystemException(SystemException.IOException, msg1);

			}
		}
	}

	/**
	 * 生成空表模板
	 * @param filePath
	 */
	public static String exportTemplate(String root,String filePath){
		

		
		
		OutputStream os = null;
		XSSFWorkbook workbook  = null;
			try {
				os = new FileOutputStream(root.split("\\|")[0]+File.separator+filePath);
			
				workbook = new XSSFWorkbook();
				
//				
//				XSSFCellStyle textStyle = workbook.createCellStyle();
//				XSSFDataFormat format = workbook.createDataFormat();
//				textStyle.setDataFormat(format.getFormat("@"));
//				cell.setCellStyle(textStyle);//设置单元格格式为"文本"
//				
			
				//表1
				Sheet sheet = workbook.createSheet("市民卡批量申请表");

				setTitle(sheet, workbook);
				
				Row row1 = sheet.createRow(0);
				logger.debug("row1==null==>[{}]",row1==null);
				Cell c = row1.createCell(0);
				logger.debug("c==null==>[{}]",c==null);
				c.setCellValue("注：带*号为必填项，填写内容时，将样例数据修改为正式数据，并且单元格格式均为文本格式。");
				// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
				sheet.addMergedRegion(new CellRangeAddress(0,0, 0, 12));
				
				
				Row row2 = sheet.createRow(1);
				 c = row2.createCell(0);
					logger.debug("c==null==>[{}]",c==null);
				c.setCellValue("样例：序号1数据。");
				
				// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
				sheet.addMergedRegion(new CellRangeAddress(1,1, 0, 1));

				Row row4 = sheet.createRow(3);
				Row row5 = sheet.createRow(4);
				String[] tableTitle = { "*序号", "*证件类型", "*证件号码", "*姓名", "*性别", "*民族", "*出生日期", "*工作单位", "*家庭电话", "*单位电话", "*手机", "*通讯地址",
						"*邮政编码" };
				
				String[] example = { "1", "身份证", "*370781198901011212", "张三", "男", "汉族", "1989/01/01", "青州市xxx公司", "(0536)12345678", "(0536)12345678", "1356363xxxx", "xx路xx号",
				"262500" };
				
				
				for (int i = 0; i < tableTitle.length; i++) {
					Cell cell = row4.createCell(i);
					cell.setCellStyle(setContentStyle(workbook, false));
					cell.setCellValue(tableTitle[i]);
					
					 cell = row5.createCell(i);
						cell.setCellStyle(setContentStyle(workbook, false));
						cell.setCellValue(example[i]);
				}
				
				// 13列内容
				for (int i = 0; i < 14; i++) {
					// 调整最适合列宽
					sheet.autoSizeColumn(i);
				}
				
				
				//表2
				
				Sheet sheet_2 = workbook.createSheet("单位基本信息表");
				setTitle2(sheet_2, workbook);
				
				Row row = sheet_2.createRow(0);
				 c = row.createCell(0);
				c.setCellValue("注：带*号为必填项");
				// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
				sheet_2.addMergedRegion(new CellRangeAddress(0,0, 0, 1));
				
				
				 row = sheet_2.createRow(2);
				 c = row.createCell(0);
				c.setCellValue("*单位名称");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("xxxx公司");// 单位名称
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(3);
				c = row.createCell(0);
				c.setCellValue("*单位简称");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(4);
				c = row.createCell(0);
				c.setCellValue("单位类型");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位类型
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(5);
				c = row.createCell(0);
				c.setCellValue("单位等级");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位等级
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(6);
				c = row.createCell(0);
				c.setCellValue("*单位地址");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位地址
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(7);
				c = row.createCell(0);
				c.setCellValue("*邮政编码");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 邮政编码
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(8);
				c = row.createCell(0);
				c.setCellValue("*联系人");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 联系人
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(9);
				c = row.createCell(0);
				c.setCellValue("*联系人电话");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 联系人电话
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(10);
				c = row.createCell(0);
				c.setCellValue("*手机");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 手机
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(11);
				c = row.createCell(0);
				c.setCellValue("传真号码");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 传真号码
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(12);
				c = row.createCell(0);
				c.setCellValue("*E-Mail");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// E-Mail
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(13);
				c = row.createCell(0);
				c.setCellValue("*单位负责人");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位负责人
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(14);
				c = row.createCell(0);
				c.setCellValue("*负责人电话");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 负责人电话
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(15);
				c = row.createCell(0);
				c.setCellValue("法人姓名");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 法人姓名
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(16);
				c = row.createCell(0);
				c.setCellValue("法人证件类型");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 法人证件类型
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(17);
				c = row.createCell(0);
				c.setCellValue("法人证件号码");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 法人证件号码
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(18);
				c = row.createCell(0);
				c.setCellValue("法人联系电话");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 法人联系电话
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(19);
				c = row.createCell(0);
				c.setCellValue("组织机构代码");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 组织机构代码
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(20);
				c = row.createCell(0);
				c.setCellValue("开户银行");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 开户银行
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(21);
				c = row.createCell(0);
				c.setCellValue("基本帐号");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 基本帐号
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(22);
				c = row.createCell(0);
				c.setCellValue("注册资金");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 注册资金
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(23);
				c = row.createCell(0);
				c.setCellValue("注册日期");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 注册日期
				c.setCellStyle(setContentStyle(workbook, false));
				
				c = row.createCell(2);
				c.setCellValue("样例：yyyy/MM/dd");// 注册日期
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(24);
				c = row.createCell(0);
				c.setCellValue("纳税登记号");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 纳税登记号
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(25);
				c = row.createCell(0);
				c.setCellValue("企业登记号");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 企业登记号
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(26);
				c = row.createCell(0);
				c.setCellValue("客户经理");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 客户经理
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(27);
				c = row.createCell(0);
				c.setCellValue("行业代码");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 行业代码
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(28);
				c = row.createCell(0);
				c.setCellValue("单位状态");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位状态
				c.setCellStyle(setContentStyle(workbook, false));

				row = sheet_2.createRow(29);
				c = row.createCell(0);
				c.setCellValue("*单位编号");
				c.setCellStyle(setContentStyle(workbook, false));
				c = row.createCell(1);
				c.setCellValue("");// 单位编号
				c.setCellStyle(setContentStyle(workbook, false));

//				row = sheet_2.createRow(31);
//				c = row.createCell(0);
//				c.setCellValue("是否加急");
//				c.setCellStyle(setContentStyle(workbook, false));
//				c = row.createCell(1);
//				c.setCellValue("");// 是否加急
//				c.setCellStyle(setContentStyle(workbook, false));

				// 2列内容
				for (int i = 0; i < 2; i++) {
					// 调整最适合列宽
					sheet_2.autoSizeColumn(i);
				}
				workbook.write(os);
			}catch (FileNotFoundException e) {
				e.printStackTrace();
				String msg1="IO流异常";
				logger.error(msg1);
				throw new SystemException(SystemException.IOException, msg1);

				}catch (IOException e) {
					e.printStackTrace();
					String msg1="IO流异常";
					logger.error(msg1);
					throw new SystemException(SystemException.IOException, msg1);
			}finally {
				try {
				if (os != null){
					os.flush();
					os.close();
				}
				if(workbook != null ){
						workbook.close();
				}
				} catch (IOException e) {
					e.printStackTrace();
					String msg1="IO流关闭异常";
					logger.error(msg1);
					throw new SystemException(SystemException.IOException, msg1);
				}
			}
			return root.split("\\|")[1]+File.separator+filePath;
			
			
	}
	
	
	//个人信息和卡号对应模板
	
	public static File batchSellCardTemplate(List<DownloadBatchSellCardVo> list,String corName,String root){
		String fileName = corName+".xlsx";
		String uploadDir=root+File.separator+PathContants.PROJECT_PATH_DOWNLOAD_BATCHSELLCARDINFO;
		String uploadPath = uploadDir+File.separator+fileName;
		
		File dir = new File(uploadDir);
		File file = new File(uploadPath);
		try {
			if(!dir.exists()){
				dir.mkdirs();
			}
			if(!file.exists()){
				file.createNewFile();
			}
		
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new SystemException(ErrorCode.OTHER_EXCEPTION, "系统创建文件异常");
		}
		OutputStream os = null;
		XSSFWorkbook workbook  = new XSSFWorkbook();
			try {
				os = new FileOutputStream(uploadPath);
				String title = "市民卡批量发卡";
				Sheet sheet = workbook.createSheet(title);

				//设置表头
				Row rowheader = sheet.createRow(1);
				Cell cellheader = rowheader.createCell(0);
				cellheader.setCellValue(title);
				cellheader.setCellStyle(setContentStyle(workbook, true));
				// 合并单元格 CellRangeAddress(a起始行号，b终止行号，c 起始列号，d终止列号）
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
				
				String[] tableTitle = { "序号", "市民卡卡号","姓名","证件类型", "证件号码",  "性别","手机" };

				Row row3 = sheet.createRow(2);
				
				for (int i = 0; i < tableTitle.length; i++) {
					Cell cell = row3.createCell(i);
					cell.setCellStyle(setContentStyle(workbook, false));
					cell.setCellValue(tableTitle[i]);
					
				}
				
				for (int i = 0; i < list.size(); i++) {
					Row row = sheet.createRow(i + 3);
					DownloadBatchSellCardVo pt = list.get(i);

					Cell c = row.createCell(0);
					c.setCellValue(i + 1);
					c.setCellType(CellType.NUMERIC);
					c.setCellStyle(setContentStyle(workbook, false));
					
					
					c = row.createCell(1);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getCitizenCardNo());
					c.setCellStyle(setContentStyle(workbook, false));
					
					c = row.createCell(2);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getCustomerName());
					c.setCellStyle(setContentStyle(workbook, false));
					
					c = row.createCell(3);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getPaperType());
					c.setCellStyle(setContentStyle(workbook, false));
					
					c = row.createCell(4);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getPaperNo());
					c.setCellStyle(setContentStyle(workbook, false));
					
					
					
					c = row.createCell(5);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getGender());
					c.setCellStyle(setContentStyle(workbook, false));
					
					c = row.createCell(6);
					c.setCellType(CellType.STRING);
					c.setCellValue(pt.getMobile());
					c.setCellStyle(setContentStyle(workbook, false));
					
				}

				// 13列内容
				for (int i = 0; i < tableTitle.length; i++) {
					// 调整最适合列宽
					sheet.autoSizeColumn(i);
				}
				
				workbook.write(os);
			}catch(Exception e){
				e.printStackTrace();
				throw new SystemException(ErrorCode.OTHER_EXCEPTION, "数据异常，下载文件失败");
				
			}finally {
				  try {
					os.close();
					workbook.close();
				} catch (Exception e) {
					throw new SystemException(ErrorCode.OTHER_EXCEPTION, "数据异常，下载文件失败");
				}
			  
			}
		
			
			
			
			return file;
	}
	

}
